﻿CREATE PROCEDURE [dbo].[proc_Project_Getlist_zxcd]
(
    @CompanyId int,
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),	
	@StartIndex int,
	@EndIndex INT,
	@noe_flag INT
)

AS
--	;WITH list As(
		


----SELECT ROW_NUMBER() OVER (ORDER BY Id)AS Row,
----CusName, SalePrice
----,OrderId

----FROM Project  Where  companyId=@CompanyId AND  Convert(nchar(10),CreateDate,120)
---- BETWEEN @StartDate and @EndDate
--Select ROW_NUMBER() OVER (ORDER BY CusName)AS Row,* from ( SELECT sum(SalePrice) SalePrice,cusname,orderid FROM (
--select id,F_YingShou  AS SalePrice,CusName,OId AS OrderId,0 AS flag,NOE_Flag From [Order] Where CompanyId=@CompanyId
--and StateId>=1 and Convert(nchar(10),AuditDate,120)BETWEEN @StartDate and @EndDate	AND (NOE_Flag<>2) 
----AND (NOE_Flag<>2)     and oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
	
--UNION 
----select * from (
--	SELECT 0 AS id,dch_chd ,CusName,OId,1 AS flag,0 AS NOE_Flag  FROM Order_Change_Hedge 
--      WHERE CompanyId=@CompanyId and Convert(nchar(10),UpTime,120) BETWEEN @StartDate and @EndDate
-- and oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
----) AS nn
--      --WHERE (NOE_Flag=@noe_flag OR @noe_flag=-1) 	
	
--) AS nb1 WHERE SalePrice!=0 GROUP BY cusname,orderid) AS nb2

--)
	
	
--Select *,(SELECT COUNT(1) FROM list) AS DataCount,(SELECT SUM(SalePrice) FROM list) AS DataSum 
--From list Where Row between @StartIndex and @EndIndex  

	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')
	
	;WITH list As(
	
--SELECT ROW_NUMBER() OVER (ORDER BY Id)AS Row,
--CusName, SalePrice
--,OrderId

--FROM Project  Where  companyId=@CompanyId AND  Convert(nchar(10),CreateDate,120)
-- BETWEEN @StartDate and @EndDate
Select ROW_NUMBER() OVER (ORDER BY flag,CusName)AS Row,* from ( SELECT sum(SalePrice) SalePrice,cusname,orderid,flag FROM (
select id,F_YingShou  AS SalePrice,CusName,OId AS OrderId,0 AS flag,NOE_Flag From [Order] Where CompanyId=@CompanyId
and StateId>=1 and financerenlingdate>= @sRq and financerenlingdate <=@eRq AND (NOE_Flag<>2) 
--AND (NOE_Flag<>2)     and oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
	
UNION 
--select * from (
	SELECT 0 AS id,Order_Change_Hedge.dch_chd ,Order_Change_Hedge.CusName,Order_Change_Hedge.OId,1 AS flag,0 AS NOE_Flag  FROM Order_Change_Hedge inner join [Order] o on  
     Order_Change_Hedge.oid =o.oid   and Order_Change_Hedge.CompanyId=@CompanyId and Order_Change_Hedge.UpTime>= @sRq and Order_Change_Hedge.UpTime<=@eRq
 and Order_Change_Hedge.oid =o.oid  and o.companyId=@CompanyId AND o.NOE_Flag<>2 
--) AS nn
      --WHERE (NOE_Flag=@noe_flag OR @noe_flag=-1) 	

union

Select id,[money],reason,gid,2 as flag,0 as NOE_Flag From FinanceMonthTable_PZ Where CompanyId=@CompanyId 
and TypeId=3  
and  CreateDate>=@sRq and CreateDate<=@eRq  and OrderType in(1,2)	
	
) AS nb1 WHERE SalePrice!=0 GROUP BY cusname,orderid,flag ) AS nb2

)
	
	
Select *,(SELECT COUNT(1) FROM list) AS DataCount,(SELECT SUM(SalePrice) FROM list) AS DataSum 
From list Where Row between @StartIndex and @EndIndex  
	RETURN
